using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Globalization;
using System.Xml;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using Colorprj.Common;
namespace Colorprj.Account
{
    class AccountManager
    {



        public List<AccTransEntity> GetAccTransList(int VchNo, string VchType)
        {
            bool result = false;
            List<AccTransEntity> ChallanList = new List<AccTransEntity>();
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "SelAccTransaction";
            DbCommand creditCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(creditCommand, "@VchType", DbType.String, VchType);
            db.AddInParameter(creditCommand, "@VchNo", DbType.Int32, VchNo);
            DataSet ChallanchaseDataSet = db.ExecuteDataSet(creditCommand);
            foreach (DataRow dr in ChallanchaseDataSet.Tables[0].Rows)
            {
                AccTransEntity phEntity = new AccTransEntity();
                phEntity.AccountId = Utility.getIntVal(dr["AccountId"]);
                phEntity.Amount = Utility.getDblVal(dr["Amount"]);
                phEntity.PartiCular = dr["Remarks"].ToString();
                phEntity.SrNo =Utility.getIntVal(dr["srNo"]);
                phEntity.VoucherDate = Convert.ToDateTime(dr["TranDate"]);
                phEntity.VoucherId = Utility.getIntVal(dr["VchNo"]);
                phEntity.VoucherType = dr["VchType"].ToString();
                //phEntity.VoucherType  = dr["ChallanNo"].ToString();

                ChallanList.Add(phEntity);
            }

            return ChallanList;
        }
        public DataSet GetAllVnos(string VchType)
        {
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelAllVchNo";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            // DataSet that will hold the returned results		
            DataSet productsDataSet = null;
            db.AddInParameter(selSPCommand, "@VchType", DbType.String, VchType);
            productsDataSet = db.ExecuteDataSet(selSPCommand);

            // Note: connection was closed by ExecuteDataSet method call 

            return productsDataSet;
        }

        public DataSet GetAllPayments()
        {
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "SelAllPayment";
            DbCommand selSPCommand = db.GetStoredProcCommand(sqlCommand);
            // DataSet that will hold the returned results		
            DataSet productsDataSet = null;

            productsDataSet = db.ExecuteDataSet(selSPCommand);

            // Note: connection was closed by ExecuteDataSet method call 

            return productsDataSet;
        }
        public bool AddPayment(Payment pEntity)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {


                    // Two operations, one to credit an account, and one to debit another
                    // account.
                    string sqlCommand = "AddPayment";
                    DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
                    db.AddInParameter(addGroupCommand, "BuyerId", DbType.Int16, pEntity.BuyerId );
                    db.AddInParameter(addGroupCommand, "Amount", DbType.Decimal , pEntity.Amount );
                    db.AddInParameter(addGroupCommand, "AdjAmount", DbType.Decimal, pEntity.AdjAmount );
                    db.AddInParameter(addGroupCommand, "Remarks", DbType.String, pEntity.Remarks);
                    db.AddInParameter(addGroupCommand, "PayDate", DbType.String, pEntity.PayDate);
                    db.AddInParameter(addGroupCommand, "PayMentType", DbType.Boolean, pEntity.PayMentType);
                    db.AddInParameter(addGroupCommand, "ChequeNo", DbType.String, pEntity.ChequeNo );
                    db.AddInParameter(addGroupCommand, "ChequeDate", DbType.Object, pEntity.ChequeDate );

                    

                    db.ExecuteNonQuery(addGroupCommand, transaction);

                    transaction.Commit();
                    result = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            }
            return result;
        }

        public int GetNextVNo(string VType)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "GetNextVNo";
            DbCommand GetVNoCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(GetVNoCommand, "vchType", DbType.String, VType);
            int VNO = Convert.ToInt16(db.ExecuteScalar(GetVNoCommand));
            return VNO;

        }

        public bool BillAdjustMent(int BuyerID)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                try
                {


                    // Two operations, one to credit an account, and one to debit another
                    // account.
                    string sqlCommand = "BillAdjustmentForBuyer";
                    DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
                    db.AddInParameter(addGroupCommand, "BuyerId", DbType.Int16, BuyerID);
            

                    db.ExecuteNonQuery(addGroupCommand, transaction);

                    transaction.Commit();
                    result = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            }
            return result;
        }
        public bool AddVoucher(List<AccTransEntity>  EntityList)
        {
            bool result = false;
            Database db = DatabaseFactory.CreateDatabase();
            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();



                try
                {
                    //foreach (AccTransEntity pEntity in EntityList)
                    //{
                       
                    //    break;
                    //}

                    foreach (AccTransEntity pEntity in EntityList)
                    {
                        // Two operations, one to credit an account, and one to debit another
                        // account.

                        
                          

                        if (pEntity.flg == 2 )
                        {
                            if (pEntity.Amount == 0)
                            {
                                string sqlDelCommand = "DelAccTransaction";
                                DbCommand delCommand = db.GetStoredProcCommand(sqlDelCommand);
                                db.AddInParameter(delCommand, "VchNo", DbType.Int16, pEntity.VoucherId);
                                db.AddInParameter(delCommand, "VchType", DbType.String, pEntity.VoucherType);
                                db.AddInParameter(delCommand, "@SrNo", DbType.Int16, pEntity.SrNo);
                                db.ExecuteNonQuery(delCommand, transaction);
                            }
                            else {
                                string sqlCommand = "updAccTransaction";

                                DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
                                db.AddInParameter(addGroupCommand, "TrCode", DbType.String, pEntity.TrCode);
                                db.AddInParameter(addGroupCommand, "AccountId", DbType.Int16, pEntity.AccountId);
                                db.AddInParameter(addGroupCommand, "VchNo", DbType.Int16, pEntity.VoucherId);
                                db.AddInParameter(addGroupCommand, "Remarks", DbType.String, pEntity.PartiCular);
                                db.AddInParameter(addGroupCommand, "amount", DbType.Double, pEntity.Amount);
                                db.AddInParameter(addGroupCommand, "VchType", DbType.String, pEntity.VoucherType);
                                db.AddInParameter(addGroupCommand, "@SrNo", DbType.Int16, pEntity.SrNo);
                                db.AddInParameter(addGroupCommand, "TranDate", DbType.Date, pEntity.VoucherDate);

                                db.ExecuteNonQuery(addGroupCommand, transaction);
                            
                            
                            }
                        }
                        else
                        {
                            string sqlCommand = "addAccTransaction";
                        
                        DbCommand addGroupCommand = db.GetStoredProcCommand(sqlCommand);
                        db.AddInParameter(addGroupCommand, "TrCode", DbType.String, pEntity.TrCode );
                        db.AddInParameter(addGroupCommand, "AccountId", DbType.Int16 , pEntity.AccountId);
                        db.AddInParameter(addGroupCommand, "VchNo", DbType.Int16, pEntity.VoucherId );
                        db.AddInParameter(addGroupCommand, "Remarks", DbType.String, pEntity.PartiCular );
                        db.AddInParameter(addGroupCommand, "amount", DbType.Double , pEntity.Amount );
                        db.AddInParameter(addGroupCommand, "VchType", DbType.String, pEntity.VoucherType );
                        db.AddInParameter(addGroupCommand, "@SrNo", DbType.Int16, pEntity.SrNo );
                        db.AddInParameter(addGroupCommand, "TranDate", DbType.Date, pEntity.VoucherDate);

                        db.ExecuteNonQuery(addGroupCommand, transaction);
                            }
                    }
                    transaction.Commit();
                    result = true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw new Exception(ex.Message);
                }
            }
            return result;
        }

    }
}
